﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Odbc;
using System.IO;
using System.Data.SQLite;
using StickersGenerator;


namespace pistolety3
{
    public partial class Form1 : Form
    {
        string user = "";
        string haslo = "";
        string conStr = "";
        string magazynier = "";
        string document = "";
        OdbcConnection con = null;
        OdbcConnection con1 = null;
        StickersGenerator.StickersGenerator gen = new StickersGenerator.StickersGenerator();
        private string role = "kasjer";
        public string Role
        {
            get { return this.role; }
            set { this.role = value; }
        }

        private int boxNumber = 1;
        public int BoxNumber
        {
            get { return this.boxNumber; }
            set { this.boxNumber = value; }
        }

        string charset = "WIN1250";
        string serwer = "jama.kan.pl";
        string baza = "/usr/interbase/data/zamowienia2.gdb";
        pistolety pist = new pistolety();
        firebird fire = new firebird();
        //StreamWriter sw = new StreamWriter(@"c:\kan\testSkan.txt");
        //SQLiteConnection conSL = null;

        public Form1()
        {
            InitializeComponent();
            //tc.Controls.Remove(tpLogowanie);
            tc.Controls.Remove(tpSkanowanie);
            tc.Controls.Remove(tpLokalizacje);
            tc.Controls.Remove(tpAdministracja);
            tc.Controls.Remove(tpBarcode);
            tc.Controls.Remove(tpUzytkownicy);
            tc.Controls.Remove(tpDrukujStickersy);
            try
            {
                //string conStrSL = "Data Source=magazyn.sqlite;Version=3;";
                //conSL = new SQLiteConnection(conStrSL);
                //conSL.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void zamknijToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //sw.Close();
            //conSL.Close();
            Application.Exit();
        }

        private void cbLista_Click(object sender, EventArgs e)
        {
            //string query = "SELECT cd.WZ_PROMAGG FROM COLLECTOR_DOCUMENTS cd order by cd.WZ_PROMAGG ";
            //DataSet ds = pist.dokumenty(query, con);
            //cbLista.DataSource = ds.Tables[0];
            //cbLista.DisplayMember = ds.Tables[0].Columns[0].Caption;
            makeListDocuments();
        }

        private void makeListDocuments()
        {
            try
            {
                cbLista.Items.Clear();
                string query = "SELECT cd.WZ_PROMAGG FROM COLLECTOR_DOCUMENTS cd order by cd.WZ_PROMAGG ";
                OdbcCommand com = new OdbcCommand(query, con);
                OdbcDataReader dr = com.ExecuteReader();
                while (dr.Read())
                {
                    cbLista.Items.Add(dr[0].ToString());
                }
                query = "SELECT cd.WZ_PROMAGG FROM COLLECTOR_DOCUMENTS cd JOIN KASJER_DOCUMENTS kd on cd.DOCUMENT_ID=kd.DOCUMENT_ID WHERE (kd.USE = 't' or kd.CLOSED = 't')";
                com = new OdbcCommand(query, con);
                dr = com.ExecuteReader();
                while (dr.Read())
                {
                    cbLista.Items.Remove(dr[0].ToString());
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnLoguj_Click(object sender, EventArgs e)
        {
            user = tbUser.Text;
            haslo = tbPasswd.Text;
            try
            {
                logowanie(user, haslo, serwer, baza, charset, Role);

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void logowanie(string login, string passwd, string serwer, string baza, string charset, string role)
        {
            Role = fire.getRole(login);
            //MessageBox.Show(role);
            conStr = @"DRIVER=Firebird/InterBase(r) driver;UID=" + login + ";PWD=" + passwd + ";DBNAME=" + serwer + ":" + baza + ";Charset=" + charset + ";ROLE=" + Role;
            con = new OdbcConnection(conStr);
            try
            {
                con.Open();
                tc.Controls.Add(tpSkanowanie);
                tc.Controls.Remove(tpLogowanie);
                if (role == "ADM")
                {
                    tc.Controls.Add(tpAdministracja);
                    tc.Controls.Add(tpUzytkownicy);
                    tc.Controls.Add(tpBarcode);
                    tc.Controls.Add(tpLokalizacje);
                    tc.Controls.Add(tpDrukujStickersy);
                }

                if (user == "SYSDBA" || user == "sysdba" || user == "lbartczak")
                {
                    tc.Controls.Add(tpAdministracja);
                    tc.Controls.Add(tpUzytkownicy);
                    tc.Controls.Add(tpBarcode);
                    tc.Controls.Add(tpLokalizacje);
                    tc.Controls.Add(tpDrukujStickersy);
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }

        private void btnOpen_Click(object sender, EventArgs e)
        {
            if (cbLista.Text != "" && cbMagazynier.Text != "")
            {
                btnAccept.Visible = true;
                btnClose.Visible = true;
                dgv.Visible = true;
                btnOpen.Visible = false;
                fillDGV(cbLista.Text);
                lblInput.Visible = true;
                tbInput.Visible = true;
                cbLista.Visible = false;
                lblLista.Visible = false;
                tbInput.Focus();
                magazynier = cbMagazynier.Text;
                document = cbLista.Text;
                cbMagazynier.Visible = false;
                lblMagazynier.Visible = false;
                btnjKartonSkanowanie.Visible = true;
                btnDrukujEtykiety.Visible = true;
                ////Sprawdzam ostatni dodany karton dla dokumentu
                string querySelectLastBox = "SELECT max( a.BOX_NUMBER) FROM BOX_DOCUMENTS a where a.DOCUMENT_ID like " + this.cbLista.Text;
                OdbcCommand comSelectLastBox = new OdbcCommand(querySelectLastBox, con);
                OdbcDataReader readerSelectLastBox = comSelectLastBox.ExecuteReader();
                if (readerSelectLastBox.Read())
                {
                    this.BoxNumber = Convert.ToInt32(readerSelectLastBox[0]);
                }
                this.lbBoxNumberSkan.Text = "Skanujemy do pudełka " + this.BoxNumber;
                pist.blockKasjerDocument(document, "use", con);//blokowanie dokumentu do edycji
                //pist.logToFile(new string[]{"open",document,magazynier,user,DateTime.UtcNow.ToString() });                
                //pist.setKasjerDocument(pist.getCollectorDocument(con), con);
                pist.logToBase("open", document, magazynier, user, DateTime.UtcNow.ToString(), con);
            }
            else
            {
                MessageBox.Show("Wybierz dokument", "Informacja", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }



        private void fillDGV(string dokument)
        {
            dokument = pist.wz2documentid(dokument, con);
            string query = "SELECT a.DOCUMENT_ID, a.POSITION_ID, a.INDEKS, a.QUANTITY FROM COLLECTOR_POSITIONS a where a.DOCUMENT_ID='" + dokument + "'";
            MessageBox.Show(query);
            OdbcDataAdapter adap4 = new OdbcDataAdapter(query, con);
            DataSet ds4 = new DataSet("pozycje");
            adap4.Fill(ds4);
            DataTable dt4 = ds4.Tables[0];
            dgv.DataSource = dt4;

            dgv.Columns[0].Width = 50;
            dgv.Columns[1].Width = 50;
            dgv.Columns[2].Width = 120;
            dgv.Columns[3].Width = 65;

            dgv.Columns[0].ReadOnly = true;
            dgv.Columns[1].ReadOnly = true;
            dgv.Columns[2].ReadOnly = true;
            dgv.Columns[3].ReadOnly = true;

            dgv.Columns[0].HeaderText = "dokument id";
            dgv.Columns[1].HeaderText = "lp.";
            dgv.Columns[2].HeaderText = "indeks";
            dgv.Columns[3].HeaderText = "ilość";

            dgv.Columns[0].Visible = false;
            dgv.Refresh();
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            btnOpen.Visible = true;
            btnAccept.Visible = false;
            btnClose.Visible = false;
            dgv.Visible = false;
            dgv.DataSource = new DataSet();
            cbLista.Text = "";
            lblInput.Visible = false;
            tbInput.Visible = false;
            cbLista.Visible = true;
            lblLista.Visible = true;
            //pist.logToFile(new string[] {"close", document,magazynier,user, DateTime.UtcNow.ToString() });
            pist.blockKasjerDocument(document, "clear", con);
            pist.logToBase("close", document, magazynier, user, DateTime.UtcNow.ToString(), con);
            magazynier = "";
            document = "";
            cbMagazynier.Visible = true;
            lblMagazynier.Visible = true;
        }

        private void btnAccept_Click(object sender, EventArgs e)
        {
            MessageBox.Show("Będzie się wysyłała mail.\n\nBędzie się generował plik.");
            btnOpen.Visible = true;
            btnAccept.Visible = false;
            btnClose.Visible = false;
            dgv.Visible = false;
            dgv.DataSource = new DataSet();
            cbLista.Text = "";
            lblInput.Visible = false;
            tbInput.Visible = false;
            cbLista.Visible = true;
            lblLista.Visible = true;
            cbMagazynier.Visible = true;
            lblMagazynier.Visible = true;
            pist.blockKasjerDocument(document, "closed", con);
            //pist.logToFile(new string[] { "save",document,magazynier,user,DateTime.UtcNow.ToString() });
            pist.logToBase("save", document, magazynier, user, DateTime.UtcNow.ToString(), con);
            magazynier = "";
            document = "";
        }

        private void tbInput_TextChanged(object sender, EventArgs e)
        {
            MessageBox.Show(tbInput.Text);
        }

        private void tbInput_KeyDown(object sender, KeyEventArgs e)
        {
            SkanowanieBarcode(e);
        }

        private void SkanowanieBarcode(KeyEventArgs e)
        {
            this.document = this.cbLista.Text;
            if (e.KeyCode == Keys.Enter)
            {
                if (tbInput.Text.Length == 13)
                {
                    string docNumber = this.cbLista.Text;
                    string indeks = pist.EAN2Indeks(tbInput.Text, con);
                    pist.scanning(indeks, document, con);
                    ////Sprawdzam do ktorego kartonu skanujemy
                    string querySelectBox = String.Format("SELECT max(a.BOX_NUMBER) FROM BOX_DOCUMENTS a where a.DOCUMENT_ID like {0} and a.PRINTED='n'", docNumber);
                    OdbcCommand comSelectLastBox = new OdbcCommand(querySelectBox, con);
                    OdbcDataReader readerSelectLastBox = comSelectLastBox.ExecuteReader();
                    int boxToInsert = 0;
                    if (readerSelectLastBox.Read())
                    {
                        boxToInsert = (Convert.ToInt32(readerSelectLastBox[0]));
                    }
                    if (boxToInsert == 0) ////zostalo wydrukowane packing list dla ostatniego pudelka i powinnismy dodac kolejne
                    {
                        this.BoxNumber = boxToInsert + 1;
                        this.lbBoxNumberSkan.Text = "Skanujemy do pudełka " + this.BoxNumber;
                        ////Dodaje kolejne pudełko do dokumentu
                        string query = "INSERT INTO BOX_DOCUMENTS ( BOX_NUMBER, DOCUMENT_ID, PRINTED) " +
                        "VALUES (" + this.BoxNumber.ToString() + "," + docNumber + ",n)";
                        OdbcCommand com = new OdbcCommand(query, con);
                        com.ExecuteNonQuery();
                    }
                    ////Sprawdza czy jest już dany model w pudełku
                    int qty = 0;
                    string querySelectBoxPos = "SELECT bp.QTY FROM BOX_DOCUMENTS bd JOIN BOX_POSITIONS bp ON bd.BOX_ID=bp.BOX_ID " +
                                               "WHERE bd.BOX_NUMBER=" + this.BoxNumber + " and bd.DOCUMENT_ID = " + docNumber + " and bp.INDEKS like '" + indeks + "'";
                    OdbcCommand comSelectBoxPos = new OdbcCommand(querySelectBoxPos, con);
                    OdbcDataReader readerSelectBoxPos = comSelectBoxPos.ExecuteReader();
                    while (readerSelectBoxPos.Read())
                    {
                        qty += Convert.ToInt32(readerSelectBoxPos[0]);
                    }
                    ////Dodaje kolejną rzecz do pudełka
                    if (qty == 0)
                    {
                        string queryInsertIntoBox = "INSERT INTO BOX_POSITIONS ( BOX_ID, INDEKS, QTY) " +
                        "VALUES ((Select bd.BOX_ID From BOX_DOCUMENTS bd WHERE bd.DOCUMENT_ID =" + docNumber + " AND bd.BOX_NUMBER = " + this.BoxNumber.ToString() + "," + indeks + "," + 1 + ")";
                        OdbcCommand comInsertIntoBox = new OdbcCommand(queryInsertIntoBox, con);
                        comInsertIntoBox.ExecuteNonQuery();
                    }
                    if (qty != 0)  ////update
                    {
                        string queryInsertIntoBox = "UPDATE BOX_POSITISONS bp set bp.QTY=" + (qty + 1) +
                     " WHERE indeks = '" + indeks + "' AND bp.BOX_ID=(Select bd.BOX_ID From BOX_DOCUMENTS bd WHERE bd.DOCUMENT_ID =" + docNumber + " AND bd.BOX_NUMBER = " + this.BoxNumber.ToString() + ")";
                        OdbcCommand comInsertIntoBox = new OdbcCommand(queryInsertIntoBox, con);
                        comInsertIntoBox.ExecuteNonQuery();
                    }
                    /*
                    int qtyDocument = pist.getQtyCollector(indeks, document, con);
                    int qtyKasjer   = pist.getQtyKasjer(indeks, document, con);                    

                    if (indeks != "")
                    {
                        query = "ERROR";
                        MessageBox.Show("Brak kodu","Brak kodu", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }

                    if (qtyDocument > 0 && qtyKasjer == 0)
                    {
                        query = "INSERT INTO KASJER_POSITIONS (DOCUMENT_ID, POSITION_ID, INDEKS, QUANTITY) VALUES ('"+document+"', '"+""+"', '"+indeks+"', 1) ";
                        //insert 
                    }

                    if (qtyDocument > 0 && qtyKasjer > 0 && qtyDocument > qtyKasjer)
                    {
                        int ilosc = Convert.ToInt32(qtyKasjer)+1;
                        query = "UPDATE KASJER_POSITIONS SET WHERE DOCUMENT_ID='"+document+"', INDEKS='"+indeks+"', QUANTITY='"+ilosc.ToString()+"'";
                        //update
                    }
                    
                    if (indeks.Length == 0)
                        MessageBox.Show("Nieznany EAN","Błąd odczytu",MessageBoxButtons.OK,MessageBoxIcon.Error);
                    */
                }
                else
                {
                    System.Media.SystemSounds.Exclamation.Play();
                    pist.logToBase(tbInput.Text + " " + "Błędny kod", document, magazynier, user, DateTime.UtcNow.ToString(), con);
                    MessageBox.Show("Niewłaściwy kod", "Błąd skanowania", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }


                //sw.WriteLine(tbInput.Text);
                // logowanie do pliku pist.logToFile(new string[] { tbInput.Text, cbLista.Text, magazynier, user, DateTime.UtcNow.ToString() });
                //logowanie do bazy 
                pist.logToBase(tbInput.Text, document, magazynier, user, DateTime.UtcNow.ToString(), con);
                tbInput.Text = "";
            }
        }

        private void tpLokalizacje_Enter(object sender, EventArgs e)
        {
            lblIloscLokalizacji.Text = "Ilość lokalizacji zajętych: " + pist.countLocation(con);
        }

        private void lokalizacje(string lokalizacja, string indeks)
        {
            DataSet ds = new DataSet();
            string query = "SELECT l.LOCATIONID, l.INDEKS, l.QUANTITY FROM LOCATION l WHERE l.lokationid='" + lokalizacja + "' AND l.indeks='" + indeks + "'";
            OdbcDataAdapter da = new OdbcDataAdapter(query, con);
            da.Fill(ds);
            dgv2.DataSource = ds.Tables[0];
            dgv2.Columns[0].Width = 50;
            dgv2.Columns[1].Width = 100;
            dgv2.Columns[2].Width = 50;

            dgv2.Columns[0].HeaderText = "lokalizacja";
            dgv2.Columns[1].HeaderText = "indeks";
            dgv2.Columns[2].HeaderText = "ilość";

            dgv2.Columns[0].ReadOnly = true;
            dgv2.Columns[1].ReadOnly = true;
            dgv2.Columns[2].ReadOnly = true;
            dgv2.Columns[2].Visible = false;
        }

        private void btnImport_Click(object sender, EventArgs e)
        {
            string filename = pist.otwieracz(@"c:\");
            if (filename != "")
                importLocation(filename);
        }

        private string isLocation(string indeks)
        {
            string value = "";
            string query = "SELECT l.LOCATIONID, l.INDEKS FROM LOCATION l where l.INDEKS='" + indeks + "'";
            OdbcCommand com = new OdbcCommand(query, con);
            OdbcDataReader dr = com.ExecuteReader();
            if (dr.Read())
            {
                value = dr[0].ToString();
            }
            return value;
        }

        private string getIndeks(string location)
        {
            string value = "";
            string query = "SELECT l.LOCATIONID, l.INDEKS FROM LOCATION l where l.LOCATIONID='" + location + "'";
            OdbcCommand com = new OdbcCommand(query, con);
            OdbcDataReader dr = com.ExecuteReader();
            if (dr.Read())
            {
                value = dr[1].ToString();
            }
            return value;
        }

        private void clearLocation(string location)
        {
            string query = "DELETE FROM LOCATION WHERE LOCATIONID='" + location + "'";
            OdbcCommand com = new OdbcCommand(query, con);
            com.ExecuteNonQuery();
        }

        private void moveIndeks(string indeks, string location, string locationOld)
        {
            string query = "INSERT INTO INDEKSTRANS (INDEKS, LOCATION, LOCATIONOLD, DATA) VALUES ('" + indeks + "', '" + location + "', '" + locationOld + "', '" + DateTime.Now + "')";
            MessageBox.Show(query);
            OdbcCommand com = new OdbcCommand(query, con);
            com.ExecuteNonQuery();
            clearLocation(locationOld);
        }

        private void importLocation(string filename)
        {
            using (StreamReader sr = new StreamReader(filename))
            {
                while (sr.Peek() >= 0)
                {
                    string[] tab = sr.ReadLine().Split(';');
                    string location = tab[0];
                    string indeks = tab[1];
                    string query = "";

                    string locationOld = isLocation(indeks);     // poprzednia lokalizacja indeksu, jeśli pusta, to nie było wcześniej indeksu na magazynie
                    string indeksOld = getIndeks(location);    // sprawdzenie czy lokalizacja zawiera indeks, jeśli tak zwraca indeks

                    if (indeksOld == "") //sprawdzenie czy lokalizacja jest pusta
                    {
                        query = "INSERT INTO LOCATION (LOCATIONID, INDEKS) VALUES ('" + tab[0] + "', '" + tab[1] + "')";
                        if (locationOld != "")//sprawdza czy indeks znajdował się gdzieś na magazynie
                        {
                            moveIndeks(indeks, location, locationOld); //zapisuje ruch towaru w tabeli INDEKSTRANS i kasuje towar z poprzedniej lokalizacji
                        }
                        OdbcCommand com = new OdbcCommand(query, con);
                        com.ExecuteNonQuery();
                    }
                    else
                    {
                        MessageBox.Show("Uwaga\nZajęta lokalizacja", "Uwaga", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                        //pist.locationArch(tab[1], lokalizacja, tab[0], con);
                        //query = "UPDATE LOCATION SET INDEKS ='" + tab[1] + "'  where LocationID='" + tab[0] + "'";
                        ////query = "UPDATE LOCATION SET LOCATIONID ='" + tab[0] + "', INDEKS = '" + tab[1] + "' where LocationID='"+tab[0]+"'";
                    }

                }
            }
            MessageBox.Show("koniec importu");
            dgv2.Refresh();
        }

        private void exportLocation(string filname)
        {
            using (StreamWriter sw = new StreamWriter(filname))
            {
                string query = "select locationid,indeks from location";
                OdbcCommand com = new OdbcCommand(query, con);
                OdbcDataReader dr = com.ExecuteReader();
                while (dr.Read())
                {
                    sw.WriteLine(dr[0].ToString() + ";" + dr[1].ToString());
                }
                sw.Close();
            }
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            string filename = pist.zamykacz(@"c:\");
            exportLocation(filename);
        }

        string UTF8ToCP1251(string value)
        {
            byte[] UTF8Bytes = System.Text.Encoding.UTF8.GetBytes(value),
            CP1251Bytes = System.Text.Encoding.Convert(System.Text.Encoding.UTF8, System.Text.Encoding.GetEncoding(1251), UTF8Bytes);
            char[] CP1251Chars = new char[System.Text.Encoding.GetEncoding(1251).GetCharCount(CP1251Bytes, 0, CP1251Bytes.Length)];
            System.Text.Encoding.GetEncoding(1251).GetChars(CP1251Bytes, 0, CP1251Bytes.Length, CP1251Chars, 0);
            return (new string(CP1251Chars));
        }

        private void cbMagazynier_Click(object sender, EventArgs e)
        {
            try
            {
                string query = "SELECT m.MAGAZYNIERID FROM MAGAZYNIERZY m WHERE m.ACTIVITY='T'";
                DataSet ds = pist.dokumenty(query, con);
                cbMagazynier.DataSource = ds.Tables[0];
                cbMagazynier.DisplayMember = ds.Tables[0].Columns[0].Caption;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void tbBarcodeIndeks_Click(object sender, EventArgs e)
        {
            //string query = "SELECT m.NAZWA,m.NR_KATALOGOWY, m.SEZON,b.ASSORT_ID, a.KOLOR,m.PRODUCENT_ID,m.KRAJ_POCH,m.ROZMIARY,p.KOD_PASKOWY,m.OKNO,a.MODEL_ID, SUM(a.ILOSC1), SUM(a.ILOSC2), SUM(a.ILOSC3), SUM(a.ILOSC4), SUM(a.ILOSC5), SUM(a.ILOSC6), SUM(a.ILOSC7) FROM KONTRAKT a join KLIENT k on k.KLIENT_ID=a.KLIENT_ID join MODEL m on m.MODEL_ID=a.MODEL_ID JOIN ASORTYMENT b on b.ASSORT_ID=m.ASSORT_ID JOIN PROCENTOWKA p on p.MODEL_ID=m.MODEL_ID where k.KIERUNEK_WYSYLEK_ID=2 and m.SEZON=" + term.sezon2id(sezon.Text, con) + " and a.ILOSC1 not like 'null' and a.ILOSC2 not like 'null' and a.ILOSC3 not like 'null' and a.ILOSC4 not like 'null' and a.ILOSC5 not like 'null' and a.ILOSC6 not like 'null' and a.ILOSC7 not like 'null' group by m.NAZWA,m.NR_KATALOGOWY,m.SEZON,b.ASSORT_ID,a.KOLOR,m.PRODUCENT_ID,m.KRAJ_POCH,m.ROZMIARY,p.KOD_PASKOWY,m.OKNO,a.MODEL_ID order by a.MODEL_ID ";
            string query = "SELECT m.NAZWA,m.NR_KATALOGOWY, m.SEZON,b.ASSORT_ID, a.KOLOR,m.PRODUCENT_ID,m.KRAJ_POCH,m.ROZMIARY,p.KOD_PASKOWY,m.OKNO,a.MODEL_ID, SUM(a.ILOSC1), SUM(a.ILOSC2), SUM(a.ILOSC3), SUM(a.ILOSC4), SUM(a.ILOSC5), SUM(a.ILOSC6), SUM(a.ILOSC7) FROM KONTRAKT a join KLIENT k on k.KLIENT_ID=a.KLIENT_ID join MODEL m on m.MODEL_ID=a.MODEL_ID JOIN ASORTYMENT b on b.ASSORT_ID=m.ASSORT_ID JOIN PROCENTOWKA p on p.MODEL_ID=m.MODEL_ID where k.KIERUNEK_WYSYLEK_ID=2 and ( m.SEZON=131727363 )and a.ILOSC1 not like 'null' and a.ILOSC2 not like 'null' and a.ILOSC3 not like 'null' and a.ILOSC4 not like 'null' and a.ILOSC5 not like 'null' and a.ILOSC6 not like 'null' and a.ILOSC7 not like 'null' group by m.NAZWA,m.NR_KATALOGOWY,m.SEZON,b.ASSORT_ID,a.KOLOR,m.PRODUCENT_ID,m.KRAJ_POCH,m.ROZMIARY,p.KOD_PASKOWY,m.OKNO,a.MODEL_ID order by a.MODEL_ID ";
            string fileName = @"c:\kan\barcode.txt";
            OdbcConnection conTerm = new OdbcConnection();
            string conStr1 = @"DRIVER=Firebird/InterBase(r) driver;UID=" + user + ";PWD=" + haslo + ";DBNAME=" + "interbase.kan.pl" + ":" + "/usr/interbase/data/kan.gdb" + ";Charset=" + charset + ";ROLE=" + Role;
            conTerm = new OdbcConnection(conStr1);
            conTerm.Open();
            pist.barcodeIndexGenerator(query, fileName, conTerm, con);
            conTerm.Close();
        }

        private void btnDrukujEtykietaRegal_Click(object sender, EventArgs e)
        {
            int regalOd = Convert.ToInt32(tbRegalFrom.Text);
            int polkaOd = Convert.ToInt32(tbPolkaFrom.Text);
            int pozycjaOd = Convert.ToInt32(tbPozycjaFrom.Text);

            int regalDo = Convert.ToInt32(tbRegalTo.Text);
            int polkaDo = Convert.ToInt32(tbPolkaTo.Text);
            int pozycjaDo = Convert.ToInt32(tbPozycjaTo.Text);
            int regal = regalOd;
            int polka = polkaOd;
            int pozycja = pozycjaOd;
            zebra3 z = new zebra3();
            StreamWriter fs = new StreamWriter(@"c:\kan\test.txt");

            while (regal <= regalDo && regal <= 999)
            {
                while (pozycja <= pozycjaDo && pozycja <= 99)
                {
                    while (polka <= polkaDo && polka <= 9)
                    {
                        string regalTxt = pist.fixStr(regal.ToString(), 3, "0");
                        string polkaTxt = polka.ToString();
                        string pozycjaTxt = pist.fixStr(pozycja.ToString(), 2, "0");
                        z.wydrukZebra(regalTxt + pozycjaTxt + polkaTxt, z.konfigurator("etykieta"), z.konfigurator("drukarka"));
                        fs.WriteLine(regalTxt + pozycjaTxt + polkaTxt);
                        polka++;
                    }
                    polka = polkaOd;
                    pozycja++;
                }
                pozycja = pozycjaOd;
                regal++;
            }
            fs.Close();
        }

        private void btnExportBarcode_Click(object sender, EventArgs e)
        {
            String plik = "";

            SaveFileDialog fDialog = new SaveFileDialog();
            fDialog.Title = "Zapisz plik csv";
            fDialog.Filter = "pliki CSV|*.csv";
            fDialog.FileName = "barcode.sv";
            fDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
            if (fDialog.ShowDialog() == DialogResult.OK)
            {
                plik = fDialog.FileName.ToString();
            }
            fDialog.AddExtension = true;
            fDialog.CheckFileExists = true;
            fDialog.CheckPathExists = true;
            fDialog.InitialDirectory = @"C:\Windows";
            fDialog.ShowHelp = true;
            pist.exportBarcode(plik, con);
        }

        private void btnBarcode2SQLite_Click(object sender, EventArgs e)
        {
            //pist.writeBarcode2SQL(con,conSL);
        }

        private void tpBarcode_Enter(object sender, EventArgs e)
        {
            lblIloscBarcodow.Text = "Ilość kodów kreskowych: " + pist.countBarcode(con).ToString();
            lblIloscIndeksow.Text = "Ilość indeksów: " + pist.countIndeks(con).ToString();
        }

        public void fillBarcode(string indeks, string barcode, string nazwa)
        {
            if (indeks == "%" || barcode == "%" || nazwa == "%")
            {
                MessageBox.Show("Niewłaściwe dane");
                return;
            }
            if (indeks == "")
                indeks = "%";
            if (barcode == "")
                barcode = "%";
            if (nazwa == "")
                nazwa = "%";

            string query = "select barcode,indeks,model_name from barcode where indeks like '" + indeks + "' and barcode like '" + barcode + "' and model_name like '" + nazwa + "'";

            OdbcDataAdapter da = new OdbcDataAdapter(query, con);
            DataSet ds = new DataSet();
            da.Fill(ds);
            DataTable dt = ds.Tables[0];
            dgvBarcode.DataSource = dt;
            dgvBarcode.Columns[0].Width = 80;
            dgvBarcode.Columns[2].Width = 190;
        }

        public void fillLocation(string indeks, string location)
        {
            try
            {
                if (indeks == "%" || location == "%")
                {
                    MessageBox.Show("Niewłaściwe dane");
                    return;
                }
                if (indeks == "")
                    indeks = "%";
                if (location == "")
                    location = "%";

                //string query = "select barcode,indeks,model_name from barcode where indeks like '" + indeks + "' and barcode like '" + barcode + "' and model_name like '" + nazwa + "'";
                string query = "SELECT l.LOCATIONID, l.INDEKS, l.QUANTITY FROM LOCATION l where l.indeks like '" + indeks + "' and l.LOCATIONID like '" + location + "'";

                OdbcDataAdapter da = new OdbcDataAdapter(query, con);
                DataSet ds = new DataSet();
                da.Fill(ds);
                DataTable dt = ds.Tables[0];
                dgv2.DataSource = dt;
                dgv2.Columns[0].Width = 80;
                dgv2.Columns[1].Width = 140;
                dgv2.Columns[2].Visible = false;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnBarcode_Click(object sender, EventArgs e)
        {
            fillBarcode(tbIndeks.Text, tbBarcode.Text, "");
        }

        private void tbBarcode_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                fillBarcode(tbIndeks.Text, tbBarcode.Text, "");
        }

        private void tbIndeks_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                fillBarcode(tbIndeks.Text, tbBarcode.Text, "");
        }

        private void btnLokalizacja_Click(object sender, EventArgs e)
        {
            fillLocation(tbIndeksLokal.Text, tbLokalizacja.Text);
        }

        private void tpSkanowanie_Enter(object sender, EventArgs e)
        {
            lblIloscDokumentow.Text = "Ilość dokumentów oczekujących: " + pist.countOpenDocument(con);
        }

        private void btnMagazynierSzukaj_Click(object sender, EventArgs e)
        {
            pist.fillMagazynierzy(dgvUzytkownicy, tbImie.Text, tbNazwisko.Text, con);
        }

        private void btnDodaj_Click(object sender, EventArgs e)
        {
            pist.insertMagazynier(tbImie.Text, tbNazwisko.Text, con);
            pist.fillMagazynierzy(dgvUzytkownicy, tbImie.Text, tbNazwisko.Text, con);
        }

        private void tpUzytkownicy_Enter(object sender, EventArgs e)
        {
            pist.fillMagazynierzy(dgvUzytkownicy, tbImie.Text, tbNazwisko.Text, con);
        }


        /// <summary>
        /// Handles the Click event of the btnKartonSkanowanie control - Drukowanie packing list.
        /// </summary>
        /// <param name="sender">The source of the event.</param>
        /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
        private void btnKartonSkanowanie_Click(object sender, EventArgs e)
        {
            string docNumber = this.cbLista.Text;
            ////Zmienia w bazie info że packink lista dla pudelka zostala wydrukowana
            string query = "UPDATE BOX_DOCUMENTS bd set bd.PRINTED='t' where bd.BOX_NUMBER=" + this.BoxNumber + " and bd.DOCUMENT_ID=" + docNumber;
            OdbcCommand com = new OdbcCommand(query, con);
            com.ExecuteNonQuery();
            ////drukuje Packing liste
            gen.DrukujPackingListMagazyn(docNumber, this.BoxNumber.ToString(), con);
        }

        private void btnStickerGenerator4x4_Click(object sender, EventArgs e)
        {
            string database = String.Empty;
            if (this.rbCoyoco.Checked)
                database = "coyoco";
            if (this.rbTatuum.Checked)
                database = "tatuum";
            string typ = String.Empty;
            if (rbTypSms.Checked)
                typ = "SMS";
            if (rbTypProd.Checked)
                typ = "PROD";
            this.gen.GenerateCarelabelUsingThread(cbKraj.Text, cbItemId.Text, cbFabryka.Text, cbDoProdukcji.Checked, cbSezon.Text, typ, "4x4", cbRozmiar32.Checked, database, loadingCircle1, con);
        }

        private void btDrukujPackingListe_Click(object sender, EventArgs e)
        {
            gen.DrukujPackingListMagazyn(this.cbLista.Text, this.cbKartonNrSkanowanie.Text, con);
        }

        private void cbKartonNrSkanowanie_Click(object sender, EventArgs e)
        {
            this.cbKartonNrSkanowanie.Items.Clear();
            string querySelectBox = String.Format("SELECT a.BOX_NUMBER FROM BOX_DOCUMENTS a where a.DOCUMENT_ID like {0}", this.cbLista.Text);
            OdbcCommand comSelecBox = new OdbcCommand(querySelectBox, con);
            OdbcDataReader readerSelectLastBox = comSelecBox.ExecuteReader();
            while (readerSelectLastBox.Read())
            {
                this.cbKartonNrSkanowanie.Items.Add(readerSelectLastBox[0]);
            }
        }


    }
}

